package Fisica.Destino;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Fisica.Conexion;
import Inteface.InterfaceFisica;
import Modelo.Destino;
import Modelo.Toner;

public class DAODestino implements InterfaceFisica{
	
	public static String SELECT = "SELECT * FROM `destino` WHERE `oficina` = ? and `seccion` = ? and `sucursal` = ?";
	
	public static String FIRST = "SELECT * FROM `destino` order by `id` asc";
	public static String LAST = "SELECT * FROM `destino` order by `id` desc";
	public static String PRE = "SELECT * FROM `destino` WHERE `id` < ? order by `id` desc";
	public static String POST = "SELECT * FROM `destino` WHERE `id` > ? order by `id` asc";
	
	
	public boolean insert(Object obj) { //ok
		Destino v = (Destino)obj;
		//INSERT INTO `Destino` (`marca`, `modelo`, `codigo`) VALUES ("HP", "A12","A1234");
		String INSERTAR = "INSERT INTO `Destino` (`oficina`, `seccion`, `sucursal`) "
		+ "VALUES (?, ?, ?);";


		try {			
			PreparedStatement st;
			ResultSet rs;
			//INSERTO
//			st = Conexion.getConexion().prepareStatement(INSERTAR);	
//			st.setString(1, v.getOficina());
//			st.setString(2, v.getSeccion());
//			st.setString(3, v.getSucursal());
//			
//			st.executeUpdate();
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			st.setString(1, v.getOficina().toUpperCase());
			st.setString(2, v.getSeccion().toUpperCase());
			st.setString(3, v.getSucursal().toUpperCase());
			
			rs = st.executeQuery();
			if(!rs.next()){
				st = Conexion.getConexion().prepareStatement(INSERTAR);	
				st.setString(1, v.getOficina().toUpperCase());
				st.setString(2, v.getSeccion().toUpperCase());
				st.setString(3, v.getSucursal().toUpperCase());
				
				st.executeUpdate();
				
				
			}else{
				System.out.println("ya existe.");
			}
				
				
			System.out.println("inserto: "+v.getId());

			return true;
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;
			
	}
	
	public boolean update(Object obj){ //ok
		Destino v = (Destino)obj;

		String UPDATE = "UPDATE `Destino` SET `oficina` = ?, `seccion` = ?, `sucursal` = ?"
		+ "WHERE `id` = ?;";


		try {			
			PreparedStatement st;
			ResultSet rs;
			//INSERTO
			st = Conexion.getConexion().prepareStatement(UPDATE);	
			st.setString(1, v.getOficina().toUpperCase());
			st.setString(2, v.getSeccion().toUpperCase());
			st.setString(3, v.getSucursal().toUpperCase());
			st.setInt(4, v.getId());
			st.executeUpdate();
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			st.setString(1, v.getOficina().toUpperCase());
			st.setString(2, v.getSeccion().toUpperCase());
			st.setString(3, v.getSucursal().toUpperCase());
			rs = st.executeQuery();
			if(rs.next())
				v.setId(rs.getInt("id"));
			System.out.println("actualizo: "+v.getId());

			return true;
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;
			
	}
	
	
	public boolean delete(Object obj){ //ok
		Destino v = (Destino)obj;
		String delete = "delete from `Destino` where `id` = ?;";
		String select = "select * from `Destino` where `id` = ?;";
		try {			
			PreparedStatement st;
			ResultSet rs;
			st = Conexion.getConexion().prepareStatement(delete);
			st.setInt(1, v.getId());
			st.executeUpdate();
			
			st = Conexion.getConexion().prepareStatement(select);	
			st.setInt(1, v.getId());
			rs = st.executeQuery();
			
			System.out.println("borro: "+v.getId());

			return !rs.next();
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;
	}
	
	public List<Object> list (){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `Destino`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Destino o = new Destino(); 
				o.setId(rs.getInt("id")); 				
				o.setOficina(rs.getString("oficina"));				
				o.setSeccion(rs.getString("seccion"));					
				o.setSucursal(rs.getString("sucursal"));			 	
				
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}
	
	
	public List<Object> listSucursal (){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `Destino` group by `sucursal`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Destino o = new Destino(); 
				o.setId(rs.getInt("id")); 				
				o.setOficina(rs.getString("oficina"));				
				o.setSeccion(rs.getString("seccion"));					
				o.setSucursal(rs.getString("sucursal"));			 	
				
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}
	
	
	public List<Object> listSecciones (String str){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `Destino` where `sucursal` = ? group by `seccion`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			st.setString(1, str);
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Destino o = new Destino(); 
				o.setId(rs.getInt("id")); 				
				o.setOficina(rs.getString("oficina"));				
				o.setSeccion(rs.getString("seccion"));					
				o.setSucursal(rs.getString("sucursal"));				 	
				
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}
	
	
	public List<Object> listOficina (String suc, String sec){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `Destino` where `sucursal` = ? and `seccion` = ? group by `oficina`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			st.setString(1, suc);
			st.setString(2, sec);
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Destino o = new Destino(); 
				o.setId(rs.getInt("id")); 				
				o.setOficina(rs.getString("oficina"));				
				o.setSeccion(rs.getString("seccion"));					
				o.setSucursal(rs.getString("sucursal"));				 	
				
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}
	
	public Object getById(int id){ //ok
		String sql = "select * from `Destino` where `id` = ?;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();			
			
			if (rs.next()){
				Destino o = new Destino(); 
				o.setId(rs.getInt("id")); 				
				o.setOficina(rs.getString("oficina"));				
				o.setSeccion(rs.getString("seccion"));					
				o.setSucursal(rs.getString("sucursal"));		 	
				return o;
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return new Destino();	
	}

	

	public static void main(String[] args) {
		Destino t = new Destino();
		t.setOficina("administracion");
		t.setSeccion("caja");
		t.setSucursal("central");
		DAODestino daot = new DAODestino();
//		daot.insert(t); //ok
		Destino t2 = (Destino) daot.getById(1); //ok
//		System.out.println("oficina: "+t2.getOficina());
//		t2.setOficina("epson");
//		if (daot.update(t2)) //ok
//			System.out.println("EXITO");
//		else
//			System.out.println("FRACASO");
//		
//		List<Object> l = daot.list(); // ok
//		Destino y;
//		for(Object x: l){
//			y = (Destino)x;
//			System.out.println("id: "+y.getId());
//			System.out.println("marca: "+y.getOficina());
//			System.out.println("modelo: "+y.getSeccion());
//			System.out.println("codigo: "+y.getSucursal());
//		}
		
//		if (daot.delete(t2)) //ok
//			System.out.println("EXITO");
//		else
//			System.out.println("FRACASO");
		
		
	}
	
	public Object getObject (PreparedStatement st){ //ok
		Destino o = new Destino(); 	
		
		try {
//			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			if (rs.next()){
				o.setId(rs.getInt("id")); 				
				o.setOficina(rs.getString("oficina"));				
				o.setSeccion(rs.getString("seccion"));					
				o.setSucursal(rs.getString("sucursal"));			
			}	
			return o;
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return new Toner();		
	}
	
	@Override
	public Object getPre(int id) {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(PRE);
			st.setInt(1, id);
			return getObject(st);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public Object getPost(int id) {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(POST);
			st.setInt(1, id);
			return getObject(st);
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public Object getFirst() {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(FIRST);
			return getObject(st);
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public Object getLast() {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(LAST);
			return getObject(st);			
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public boolean exist(Object obj) { //ok
		Destino v = (Destino)obj;
		
		try {			
			PreparedStatement st;
			ResultSet rs;
			
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			st.setString(1, v.getOficina());
			st.setString(2, v.getSeccion());
			st.setString(3, v.getSucursal());
			
			rs = st.executeQuery();
			if(rs.next())
				v.setId(rs.getInt("id"));
			System.out.println("existe: "+v.getId());

			return true;
			
		}catch(Exception e){
			System.out.println("exception al existir: "+v.getId());
			e.printStackTrace();
		}
		return false;
			
	}
	
	public Object get(Object obj) { //ok
		Destino v = (Destino)obj;
		
		try {			
			PreparedStatement st;
			ResultSet rs;
			
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			st.setString(1, v.getOficina());
			st.setString(2, v.getSeccion());
			st.setString(3, v.getSucursal());
			
			rs = st.executeQuery();
			if(rs.next())
				v.setId(rs.getInt("id"));
			System.out.println("existe: "+v.getId());

			return v;
			
		}catch(Exception e){
			System.out.println("exception al existir: "+v.getId());
			e.printStackTrace();
		}
		return new Destino();
			
	}
	
}
